package ca.mcgill.mni.bic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

public class TarchiveDatabase {
	Connection conn;
	
	static Logger logger = Logger.getLogger(TarchiveDatabase.class);
	
	/**
	 * Constructor for TarchiveDatabase - initializes database connection.
	 */
	public TarchiveDatabase() {
		logger.debug("Configuring database connection");
		Config config = Config.singleton(); 
 
		// Create encrypter/decrypter class
        Encrypter encrypter = new Encrypter(config.dbCode);
 
        String plainHost = null;
        String plainUser = null;
        String plainPass = null;
        String plainDB = null;
        try {
        	plainHost = encrypter.decrypt(config.dbHostname);
        	plainUser = encrypter.decrypt(config.dbUser);
        	plainPass = encrypter.decrypt(config.dbPassword);
        	plainDB = encrypter.decrypt(config.dbDatabase);
        } catch (Exception e) {
        	System.err.println("Could not decrypt config settings.");
        	logger.fatal("Could not decrypt config settings.");
        	System.exit(1);
        }

		if(plainHost == null || plainUser == null || 
				plainPass == null || plainDB == null) {
			logger.fatal("Could not obtain database connection preferences.");
			System.exit(1);
		} else {
			try {
				// The newInstance() call is a work around for some
				// broken Java implementations
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				conn = DriverManager.getConnection("jdbc:mysql://" + plainHost + 
						"/" + plainDB,  plainUser, plainPass);
				logger.debug("Connected to database");
			} catch (SQLException ex) {
				// handle any errors
				System.err.println("Could not connect to database.");
				System.out.println("SQLException: " + ex.getMessage());
				logger.error("Could not connect to database: " + ex.getMessage());
				System.exit(1);
			} catch (Exception ex) {
				System.err.println("Could not load MySQL driver.");
				logger.fatal("Could not load MySQL driver");
				System.exit(1);
			}
		}
	}
	
	/**
	 * Gets a list of StudyListings for the specified criteria.
	 * @param site The centerName of the site, if only one site is desired.
	 * @param hideReviewed Hide reviewed studies if true.  
	 * @param hideSent Hide sent studies if true.
	 * @param patientName The patient name of the patient to find.
	 * @return
	 */
	public List<StudyListing> getListOfStudies(String site, Boolean hideReviewed, Boolean hideSent, String patientName) {
		// assume that conn is an already created JDBC connection
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		List<StudyListing> studyListings = new ArrayList<StudyListing>();
		String query = "";
		
		try {
			query = "SELECT TarchiveID, DicomArchiveID, PatientName, PatientID, DateAcquired, AcquisitionCount, DateSent, PendingTransfer FROM tarchive WHERE ";
			List<String> whereBits = new ArrayList<String>();
			
			int currentWhereIndex = 1;
			
			// limit to a specific site
			int centerNameWhereIndex = 0;
			if(site != null) {
				whereBits.add("CenterName = ?");
				centerNameWhereIndex = currentWhereIndex;
				currentWhereIndex++;
			}
			
			// limit to a specific patient name
			int patientNameWhereIndex = 0;
			if(patientName != null) {
				whereBits.add("PatientName LIKE ?");
				patientNameWhereIndex = currentWhereIndex;
				currentWhereIndex++;
			}
			
			// hide reviewed
			if(hideReviewed) {
				whereBits.add("DateSent IS NULL AND PendingTransfer=0");
			}
			
			// hide sent
			if(hideSent) {
				whereBits.add("DateSent IS NULL");
			}
			
			// build the query
			if(whereBits.size() > 0) {
				for(int i = 0; i < whereBits.size(); i++) {
					if(i > 0) query += " AND ";
					query += whereBits.get(i);
				}
			} else {
				query += "1";
			}
		
			query += " ORDER BY DateAcquired ASC, PatientName ASC";
			
			// prepare the statement
			stmt = conn.prepareStatement(query);

			// specify which site to limit to
			if(site != null) {
				stmt.setString(centerNameWhereIndex, site);
			}
			
			// specify which patient to limit to
			if(patientName != null) {
				stmt.setString(patientNameWhereIndex, patientName + "%");
			}
			
			// now execute the query and build the listings
			rs = stmt.executeQuery();
			
			while(rs.next()) {
				StudyListing curListing = new StudyListing(
						rs.getInt("TarchiveID"),
						rs.getString("DicomArchiveID"), 
						rs.getString("PatientName"), 
						rs.getString("PatientID"), 
						rs.getDate("DateAcquired"),
						rs.getInt("AcquisitionCount"),
						rs.getDate("DateSent"),
						rs.getBoolean("PendingTransfer"));
				
				studyListings.add(curListing);
			}
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			System.out.println("Query attempted: " + query);
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
					rs = null;
				}
			}
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
					stmt = null;
				}	
			}
		}

		return studyListings;
	}
	
	/**
	 * Generates a Study, finding the appropriate data for a given tarchiveID.
	 * @param tarchiveID The tarchive ID.
	 * @return The Study created.
	 */
	public Study studyFactory(Integer tarchiveID) {
		// initialize Study
		Study study = new Study();

		// select the study data from the database, assuming that conn is an already created JDBC connection
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.prepareStatement("SELECT TarchiveID, DicomArchiveID, PatientName, PatientID, PatientGender, PatientDoB, DateAcquired, ArchiveLocation FROM tarchive WHERE TarchiveID = ?");
			stmt.setInt(1, tarchiveID);
			rs = stmt.executeQuery();
			
			if(rs.next()) {
				study.tarchiveID = rs.getInt("TarchiveID");
				study.studyUID = rs.getString("DicomArchiveID").trim();
				study.patientName = rs.getString("PatientName").trim();
				study.patientID = rs.getString("PatientID").trim();
				try {
					study.patientSex = rs.getString("PatientGender").trim();
				} catch (NullPointerException e) {}
				try {
					study.patientDoB = rs.getDate("PatientDoB");
				} catch (NullPointerException e) {}
				
				try {
					study.studyDate = rs.getDate("DateAcquired");
				} catch (NullPointerException e) {}
				
				study.archiveLocation = rs.getString("ArchiveLocation").trim();
				
				if(study.patientName == null) study.patientName = "";
				if(study.patientID == null) study.patientID = "";
				if(study.patientSex == null) study.patientSex = "";
			}
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
					rs = null;
				}
			}
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
					stmt = null;
				}	
			}
		}
		
		
		// select the series data from the database, assuming that conn is an already created JDBC connection
		stmt = null;
		rs = null;
		
		try {
			stmt = conn.prepareStatement("SELECT SeriesNumber, SeriesDescription, SequenceName, EchoTime, RepetitionTime, InversionTime, SliceThickness, PhaseEncoding, NumberOfFiles FROM tarchive_series WHERE TarchiveID = ?");
			stmt.setInt(1, tarchiveID);
			rs = stmt.executeQuery();
			
			while(rs.next()) {
				Series series = new Series(study);
				try {
					series.seriesNumber      = rs.getInt("SeriesNumber");
				} catch (NullPointerException e) {
					series.seriesNumber = null;
				}
				try {
					series.seriesDescription = rs.getString("SeriesDescription").trim();
				} catch (NullPointerException e) {
					series.seriesDescription = "";
				}
				try {
					series.sequenceName      = rs.getString("SequenceName").trim();
				} catch (NullPointerException e) {
					series.sequenceName = "";
					}
				try {
					series.echoTime          = rs.getDouble("EchoTime");
				} catch (NullPointerException e) {
					series.echoTime = null;
					}
				try {
					series.repetitionTime    = rs.getDouble("RepetitionTime");
				} catch (NullPointerException e) {
					series.repetitionTime = null;
					}
				try {
					series.inversionTime     = rs.getDouble("InversionTime");
				} catch (NullPointerException e) {
					series.inversionTime = null;
					}
				try {
					series.sliceThickness    = rs.getDouble("SliceThickness");
				} catch (NullPointerException e) {
					series.sliceThickness = null;
					}
				try {
					series.phaseEncoding     = rs.getString("PhaseEncoding").trim();
				} catch (NullPointerException e) {
					series.phaseEncoding = "";
					}
				try {
					series.numSlices         = rs.getInt("NumberOfFiles");
				} catch (NullPointerException e) {
					series.numSlices = null;
					}
				
				study.series.add(series);
			}
			
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
					rs = null;
				}
			}
			
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
					stmt = null;
				}	
			}
		}
		
		return study;
	}
}
